import pymysql
 
class MySQLHelper:
    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.connection = None
        self.cursor = None
 
    def connect(self):
        self.connection = pymysql.connect(
            host=self.host, user=self.user, password=self.password,
            database=self.database, port=self.port, charset=self.charset
        )
        self.cursor = self.connection.cursor()
 
    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
 
    def execute(self, sql, args=()):
        self.connect()
        result = self.cursor.execute(sql, args)
        self.close()
        return result;
 
    def fetchall(self, sql, args=()):
        self.connect()
        self.cursor.execute(sql, args)
        result = self.cursor.fetchall()
        self.close()
        return result
 
    def insert(self, table, data):
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
        self.execute(sql, tuple(data.values()))
 
    def update(self, table, data, condition):
        sets = ', '.join(['{} = %s'.format(k) for k in data])
        sql = 'UPDATE {table} SET {sets} WHERE {condition}'.format(table=table, sets=sets, condition=condition)
        self.execute(sql, tuple(data.values()))
 
    def delete(self, table, condition):
        sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
        self.execute(sql)